package com.dong.zhaofengcontact2018.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.ContactsContract;
import android.widget.AutoCompleteTextView;


import com.dong.zhaofengcontact2018.model.Folk;

import java.util.ArrayList;
import java.util.List;

public class SqlMethod {
    Context context;
    SQLHelper dbOpenHelper;
    SQLiteDatabase db;
    public static String KEY_TABLE = "folk";
    // 数据表，各个字段
    public static String KEY_ID = "_id";
    public static String KEY_UNIT = "unit";
    public static String KEY_DEPT = "dept";
    public static String KEY_RANK = "rank";
    public static String KEY_OFFICE = "office";
    public static String KEY_ROOMNUMBER = "roomNumber";
    public static String KEY_INPHONES = "inPhones";
    public static String KEY_OUTPHONES = "outPhones";
    public static String KEY_NAME = "name";
    public static String KEY_NICKNAME = "nickName";
    public static String KEY_PHONES = "phones";
    public static String KEY_ADDRESS = "address";
    public static String KEY_INGROUP = "inGroup";
    public static String KEY_LASTCALLS = "lastCalls";
    // TODO: 2018/8/15
    public static String KEY_QQ = "qq";
    public static String KEY_WEIXIN = "weixin";
    public static String KEY_EMAIL = "email";

    public static String[] columns = new String[]{
            KEY_ID,
            KEY_UNIT,
            KEY_DEPT,
            KEY_RANK,
            KEY_OFFICE,
            KEY_ROOMNUMBER,
            KEY_INPHONES,
            KEY_OUTPHONES,
            KEY_NAME,
            KEY_NICKNAME,
            KEY_PHONES,
            KEY_ADDRESS,
            KEY_INGROUP,
            KEY_LASTCALLS,
            // TODO: 2018/8/15
            KEY_QQ,
            KEY_WEIXIN,
            KEY_EMAIL,
    };

    public static String[] columnsHZ = new String[]{
            "编号",//  KEY_ID,
            "单位",//  KEY_UNIT,
            "部门",//  KEY_DEPT,
            "身份",//  KEY_RANK,
            "办公室",//  KEY_OFFICE,
            "门号",//  KEY_ROOMNUMBER,
            "内线",//  KEY_INPHONES,
            "外线",//  KEY_OUTPHONES,
            "姓名",//  KEY_NAME,
            "绰号",//  KEY_NICKNAME,
            "电话",//  KEY_PHONES,
            "住址",//  KEY_ADDRESS,
            "分组",//  KEY_INGROUP,
            "热线",//  KEY_LASTCALLS
            // TODO: 2018/8/15
            "QQ",
            "微信",
            "邮箱",
    };


    public SqlMethod(Context context) {
        this.context = context;
        this.dbOpenHelper = new SQLHelper(context);
        db = dbOpenHelper.getWritableDatabase();
    }

    /**
     * 添加记录
     */
    public void save(Folk folk) {
        db.beginTransaction();// 开启事务
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put(KEY_UNIT, folk.getUnit());
            contentValues.put(KEY_DEPT, folk.getDept());
            contentValues.put(KEY_RANK, folk.getRank());
            contentValues.put(KEY_OFFICE, folk.getOffice());
            contentValues.put(KEY_ROOMNUMBER, folk.getRoomNumber());
            contentValues.put(KEY_INPHONES, folk.getInPhones());
            contentValues.put(KEY_OUTPHONES, folk.getOutPhones());
            contentValues.put(KEY_NAME, folk.getName());
            contentValues.put(KEY_NICKNAME, folk.getNickName());
            contentValues.put(KEY_PHONES, folk.getPhones());
            contentValues.put(KEY_ADDRESS, folk.getAddress());
            contentValues.put(KEY_INGROUP, folk.getInGroup());
            contentValues.put(KEY_LASTCALLS, folk.getLastCalls());
            // TODO: 2018/8/15
            contentValues.put(KEY_QQ,folk.getQq());
            contentValues.put(KEY_WEIXIN,folk.getWeixin());
            contentValues.put(KEY_EMAIL,folk.getEmail());
            
            db.insert(KEY_TABLE, null, contentValues);
            db.setTransactionSuccessful();// 设置事务的标志为True
        } finally {
            db.endTransaction();// 结束事务,有两种情况：commit,rollback,
            // 事务的提交或回滚是由事务的标志决定的,如果事务的标志为True，事务就会提交，否侧回滚,默认情况下事务的标志为False
        }
    }

    /**
     * 添加记录列表
     */
    public void saveFolks(List<Folk> folks) {
        for (Folk folk : folks) {
            save(folk);
        }
    }

    public Folk findFolkById(String _id) {
        String selection = KEY_ID + "=?";
        String[] selectionArgs = new String[]{_id};
        String limit = "1";
        Cursor cursor = db.query(KEY_TABLE, columns, selection, selectionArgs, null, null, null, limit);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        cursor.moveToFirst();
        // 封装 Folk
        Folk folk = new Folk();
        folk.set_id(cursor.getString(cursor.getColumnIndex(KEY_ID)));
        folk.setUnit(cursor.getString(cursor.getColumnIndex(KEY_UNIT)));
        folk.setDept(cursor.getString(cursor.getColumnIndex(KEY_DEPT)));
        folk.setRank(cursor.getString(cursor.getColumnIndex(KEY_RANK)));
        folk.setOffice(cursor.getString(cursor.getColumnIndex(KEY_OFFICE)));
        folk.setRoomNumber(cursor.getString(cursor.getColumnIndex(KEY_ROOMNUMBER)));
        folk.setInPhones(cursor.getString(cursor.getColumnIndex(KEY_INPHONES)));
        folk.setOutPhones(cursor.getString(cursor.getColumnIndex(KEY_OUTPHONES)));
        folk.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
        folk.setNickName(cursor.getString(cursor.getColumnIndex(KEY_NICKNAME)));
        folk.setPhones(cursor.getString(cursor.getColumnIndex(KEY_PHONES)));
        folk.setAddress(cursor.getString(cursor.getColumnIndex(KEY_ADDRESS)));
        folk.setInGroup(cursor.getString(cursor.getColumnIndex(KEY_INGROUP)));
        folk.setLastCalls(cursor.getString(cursor.getColumnIndex(KEY_LASTCALLS)));
        // TODO: 2018/8/15
        folk.setQq(cursor.getString(cursor.getColumnIndex(KEY_QQ)));
        folk.setWeixin(cursor.getString(cursor.getColumnIndex(KEY_WEIXIN)));
        folk.setEmail(cursor.getString(cursor.getColumnIndex(KEY_EMAIL)));
        cursor.close();
        return folk;
    }

    /**
     * 条件查询
     *
     * @param selection
     * @param selectionArgs
     * @return
     */
    public List<Folk> findFolkListByCondition(String selection, String[] selectionArgs) {
        Cursor cursor = db.query(KEY_TABLE, columns, selection, selectionArgs, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        List<Folk> folkList = new ArrayList<>();
        Folk folk;
        while (cursor.moveToNext()) {
            // 逐一封装 Folk
            folk = new Folk();
            folk.set_id(cursor.getString(cursor.getColumnIndex(KEY_ID)));
            folk.setUnit(cursor.getString(cursor.getColumnIndex(KEY_UNIT)));
            folk.setDept(cursor.getString(cursor.getColumnIndex(KEY_DEPT)));
            folk.setRank(cursor.getString(cursor.getColumnIndex(KEY_RANK)));
            folk.setOffice(cursor.getString(cursor.getColumnIndex(KEY_OFFICE)));
            folk.setRoomNumber(cursor.getString(cursor.getColumnIndex(KEY_ROOMNUMBER)));
            folk.setInPhones(cursor.getString(cursor.getColumnIndex(KEY_INPHONES)));
            folk.setOutPhones(cursor.getString(cursor.getColumnIndex(KEY_OUTPHONES)));
            folk.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
            folk.setNickName(cursor.getString(cursor.getColumnIndex(KEY_NICKNAME)));
            folk.setPhones(cursor.getString(cursor.getColumnIndex(KEY_PHONES)));
            folk.setAddress(cursor.getString(cursor.getColumnIndex(KEY_ADDRESS)));
            folk.setInGroup(cursor.getString(cursor.getColumnIndex(KEY_INGROUP)));
            folk.setLastCalls(cursor.getString(cursor.getColumnIndex(KEY_LASTCALLS)));
            // TODO: 2018/8/15
            folk.setQq(cursor.getString(cursor.getColumnIndex(KEY_QQ)));
            folk.setWeixin(cursor.getString(cursor.getColumnIndex(KEY_WEIXIN)));
            folk.setEmail(cursor.getString(cursor.getColumnIndex(KEY_EMAIL)));
            folkList.add(folk);
        }
        cursor.close();
        return folkList;
    }

    public Cursor findFolkCursor() {
        Cursor cursor = db.query(true, KEY_TABLE, columns, null, null,
                null, null, null, null);
        if (cursor == null) {
            return null;
        }
        return cursor;
    }

    public List<Folk> findFolkList() {
        Cursor cursor = db.query(true, KEY_TABLE, columns, null, null,
                null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        List<Folk> folkList = new ArrayList<>();
        Folk folk;
        while (cursor.moveToNext()) {
            // 逐一封装 Folk
            folk = new Folk();
            folk.set_id(cursor.getString(cursor.getColumnIndex(KEY_ID)));
            folk.setUnit(cursor.getString(cursor.getColumnIndex(KEY_UNIT)));
            folk.setDept(cursor.getString(cursor.getColumnIndex(KEY_DEPT)));
            folk.setRank(cursor.getString(cursor.getColumnIndex(KEY_RANK)));
            folk.setOffice(cursor.getString(cursor.getColumnIndex(KEY_OFFICE)));
            folk.setRoomNumber(cursor.getString(cursor.getColumnIndex(KEY_ROOMNUMBER)));
            folk.setInPhones(cursor.getString(cursor.getColumnIndex(KEY_INPHONES)));
            folk.setOutPhones(cursor.getString(cursor.getColumnIndex(KEY_OUTPHONES)));
            folk.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
            folk.setNickName(cursor.getString(cursor.getColumnIndex(KEY_NICKNAME)));
            folk.setPhones(cursor.getString(cursor.getColumnIndex(KEY_PHONES)));
            folk.setAddress(cursor.getString(cursor.getColumnIndex(KEY_ADDRESS)));
            folk.setInGroup(cursor.getString(cursor.getColumnIndex(KEY_INGROUP)));
            folk.setLastCalls(cursor.getString(cursor.getColumnIndex(KEY_LASTCALLS)));
            // TODO: 2018/8/15
            folk.setQq(cursor.getString(cursor.getColumnIndex(KEY_QQ)));
            folk.setWeixin(cursor.getString(cursor.getColumnIndex(KEY_WEIXIN)));
            folk.setEmail(cursor.getString(cursor.getColumnIndex(KEY_EMAIL)));
            folkList.add(folk);
        }
        cursor.close();
        return folkList;
    }


    /**
     * 查询 某一字段,返回 这一字段的列表
     *
     * @param field
     * @return
     */
    public List<String> findFolkFieldList(String field) {
        Cursor cursor = db.query(true, KEY_TABLE, columns, null, null,
                null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        List<String> fieldList = new ArrayList<>();
        while (cursor.moveToNext()) {
            fieldList.add(cursor.getString(cursor.getColumnIndex(field)));
        }
        cursor.close();
        return fieldList;
    }

    /**
     * 删除记录
     *
     * @param _id 记录ID
     */
    public void delete(String _id) {
        String whereClause = KEY_ID + "=?";
        String[] whereArgs = new String[]{_id};
        db.delete(KEY_TABLE, whereClause, whereArgs);
    }

    /**
     * 更新记录
     *
     * @param newFolk
     */
    public void updateOrSave(Folk newFolk) {
        if (newFolk == null) {
            return;
        }
        Folk oldFolkFromDatabase = findFolkById(newFolk.get_id());
        if (oldFolkFromDatabase == null) {
            save(newFolk);
            return;
        }
        // 更新操作
        db.beginTransaction();// 开启事务
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put(KEY_UNIT, newFolk.getUnit());
            contentValues.put(KEY_DEPT, newFolk.getDept());
            contentValues.put(KEY_RANK, newFolk.getRank());
            contentValues.put(KEY_OFFICE, newFolk.getOffice());
            contentValues.put(KEY_ROOMNUMBER, newFolk.getRoomNumber());
            contentValues.put(KEY_INPHONES, newFolk.getInPhones());
            contentValues.put(KEY_OUTPHONES, newFolk.getOutPhones());
            contentValues.put(KEY_NAME, newFolk.getName());
            contentValues.put(KEY_NICKNAME, newFolk.getNickName());
            contentValues.put(KEY_PHONES, newFolk.getPhones());
            contentValues.put(KEY_ADDRESS, newFolk.getAddress());
            contentValues.put(KEY_INGROUP, newFolk.getInGroup());
            contentValues.put(KEY_LASTCALLS, newFolk.getLastCalls());
            // TODO: 2018/8/15
            contentValues.put(KEY_QQ, newFolk.getQq());
            contentValues.put(KEY_WEIXIN, newFolk.getWeixin());
            contentValues.put(KEY_EMAIL,newFolk.getEmail());
            String whereClause = KEY_ID + "=?";
            String[] whereArgs = new String[]{newFolk.get_id()};
            db.update(KEY_TABLE, contentValues, whereClause, whereArgs);
            db.setTransactionSuccessful();// 设置事务的标志为True
        } finally {
            db.endTransaction();// 结束事务,有两种情况：commit,rollback,
            // 事务的提交或回滚是由事务的标志决定的,如果事务的标志为True，事务就会提交，否侧回滚,默认情况下事务的标志为False
        }
    }

    /**
     * 分页获取记录
     *
     * @param offset    跳过前面多少条记录
     * @param maxResult 每页获取多少条记录
     * @return
     */
    public List<Folk> getPaginationForkList(int offset, int maxResult) {
        List<Folk> persons = new ArrayList<Folk>();
        String limit = offset + "," + maxResult;
        Cursor cursor = db.query(true, KEY_TABLE, columns, null,
                null, null, null, null, limit);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        List<Folk> folkList = new ArrayList<>();
        Folk folk;
        while (cursor.moveToNext()) {
            // 逐一封装 Folk
            folk = new Folk();
            folk.set_id(cursor.getString(cursor.getColumnIndex(KEY_ID)));
            folk.setUnit(cursor.getString(cursor.getColumnIndex(KEY_UNIT)));
            folk.setDept(cursor.getString(cursor.getColumnIndex(KEY_DEPT)));
            folk.setRank(cursor.getString(cursor.getColumnIndex(KEY_RANK)));
            folk.setOffice(cursor.getString(cursor.getColumnIndex(KEY_OFFICE)));
            folk.setRoomNumber(cursor.getString(cursor.getColumnIndex(KEY_ROOMNUMBER)));
            folk.setInPhones(cursor.getString(cursor.getColumnIndex(KEY_INPHONES)));
            folk.setOutPhones(cursor.getString(cursor.getColumnIndex(KEY_OUTPHONES)));
            folk.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
            folk.setNickName(cursor.getString(cursor.getColumnIndex(KEY_NICKNAME)));
            folk.setPhones(cursor.getString(cursor.getColumnIndex(KEY_PHONES)));
            folk.setAddress(cursor.getString(cursor.getColumnIndex(KEY_ADDRESS)));
            folk.setInGroup(cursor.getString(cursor.getColumnIndex(KEY_INGROUP)));
            folk.setLastCalls(cursor.getString(cursor.getColumnIndex(KEY_LASTCALLS)));
            // TODO: 2018/8/15
            folk.setQq(cursor.getString(cursor.getColumnIndex(KEY_QQ)));
            folk.setWeixin(cursor.getString(cursor.getColumnIndex(KEY_WEIXIN)));
            folk.setEmail(cursor.getString(cursor.getColumnIndex(KEY_EMAIL)));
            folkList.add(folk);
        }
        cursor.close();
        return folkList;
    }

    /**
     * 获取记录总数
     *
     * @return
     */
    public long getCount() {
        String[] fs = new String[]{KEY_ID};
        Cursor cursor = db.query(true, KEY_TABLE, fs, null, null,
                null, null, null, null);
        if (cursor == null) {
            return 0;
        }
        return cursor.getCount();
    }


    /**
     * 更新 电话薄中多电话之间的逗号
     */
    public void tempUpdate() {
        List<Folk> folks = findFolkList();
        String phone;
        Folk currentFolk;
        for (int i = 0; i < folks.size(); i++) {
            phone = folks.get(i).getPhones();
            if (phone.contains("|")) {
                phone = phone.replace("|", ",");
                currentFolk = findFolkById(folks.get(i).get_id());
                currentFolk.setPhones(phone);
                updateOrSave(currentFolk);
            }
        }
    }

}
